--select top 10 * from tblCXCRMCallStatus Order By ModifiedDate DESC

--select top 10 * from tblCEUsrProfile ORder By ModifiedDate dESC
/****** Object:  StoredProcedure [dbo].[sprocCXCRMAutoLeadAssignment]    Script Date: 12/03/2012 16:34:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sprocCXCRMAutoCallBackLeadReAssignment]
/* '===============================================================  
 '   NAME				:	[sprocCXCRMAutoCallBackLeadReAssignment]
 '   DATE CREATED		:	03 Dec 2012 6:09 PM SG
 '   CREATED BY			:	Phyo Kyaw Thant  
 '   CREATED FOR		:	CompareXpress - http://www.gluaygluay.com/
 '   FUNCTION			:	Auto call back lead re-assignment of 21 days old call status
  '===============================================================*/ 
AS

Declare @CXCRMID bigint
Declare @AgentID bigint
Declare @AgentLeadID bigint
Declare @AgentCount smallint
Declare @NewCustomerCount int
Declare @Counter int
Declare @MaxCounter int
Declare @CustomerCount int

SET @AgentCount = 5
SET @MaxCounter = 1
SET @Counter = 1

select @CustomerCount = Count(CS.CustomerID) from 
tblCXCRMCallStatus CS INNER JOIN tblCEUsrProfile UP 
ON CS.CustomerID = UP.RefCEUserID
Where (UP.NInfo7 IS NULL OR UP.NInfo7 = 1) 
And CS.CallFinishType = 2
--And DateDiff(day, CS.CreatedDate, GetDate()) > 21
--And DateDiff(day, GetDate(), CS.CallBackDateTime) > 1
-- ****** TESTING >> NEED TO REMOVE ******
And DateDiff(day, GetDate(), CS.CallBackDateTime) = - 12
And CS.CallTypeID = 100


SET @MaxCounter = (@CustomerCount / @AgentCount) * @AgentCount

Select @CustomerCount CustomerCount, @MaxCounter MaxCounter

--select  DateDiff(day, GetDate(), CallBackDateTime), CS.* from 
--tblCXCRMCallStatus CS INNER JOIN tblCEUsrProfile UP 
--ON CS.CustomerID = UP.RefCEUserID
--Where (UP.NInfo7 IS NULL OR UP.NInfo7 = 1) 
--And CS.CallFinishType = 2
----And DateDiff(day, CS.CreatedDate, GetDate()) = 21
----And DateDiff(day, GetDate(), CS.CallBackDateTime) > 1
--And DateDiff(day, GetDate(), CS.CallBackDateTime) = - 12 -- NEED TO REMOVE
--And CS.CallTypeID = 100
--Order By CS.ModifiedDate DESC


Declare curCallBack cursor for
select  CS.CXCRMID, CS.AgentLeadID, CallBackAgentID from 
tblCXCRMCallStatus CS INNER JOIN tblCEUsrProfile UP 
ON CS.CustomerID = UP.RefCEUserID
Where (UP.NInfo7 IS NULL OR UP.NInfo7 = 1) 
And CS.CallFinishType = 2
--And DateDiff(day, CS.CreatedDate, GetDate()) = 21
--And DateDiff(day, GetDate(), CS.CallBackDateTime) > 1
-- ****** TESTING >> NEED TO REMOVE ******
And DateDiff(day, GetDate(), CS.CallBackDateTime) = - 12 
And CS.CallTypeID = 100
Order By CS.ModifiedDate DESC

SELECT TOP 1 @AgentID = CEID from tblCEUser Where CEID IN (547, 18808, 8543, 18813, 8546, 18818) Order By NewID()
	
Select @AgentID RandomAgentID

OPEN curCallBack
	FETCH NEXT FROM curCallBack INTO @CXCRMID, @AgentLeadID, @AgentID
	WHILE (@@fetch_status <> -1 AND (@MaxCounter > @Counter))
	BEGIN	
		--547	broker1@gluaygluay.com
		--18808	broker3@gluaygluay.com
		--8543	broker5@gluaygluay.com
		--18813	broker6@gluaygluay.com
		--8546	broker7@gluaygluay.com
		--18818	broker8@comparexpress.co.th
		
		IF @AgentID = 547 BEGIN SET @AgentID = 18808 END
		ELSE IF @AgentID = 18808 BEGIN SET @AgentID = 8543 END
		ELSE IF @AgentID = 8543 BEGIN SET @AgentID = 18813 END
		ELSE IF @AgentID = 18813 BEGIN SET @AgentID = 8546 END
		ELSE IF @AgentID = 8546 BEGIN SET @AgentID = 18818 END
		ELSE IF @AgentID = 18818 BEGIN SET @AgentID = 547 END
		
		--Select @AgentID AgentID, CallBackAgentID, CXCRMID, AgentLeadID FROM tblCXCRMCallStatus
		--WHERE CXCRMID = @CXCRMID AND AgentLeadID = @AgentLeadID
		
		Update tblCXCRMCallStatus SET CallBackAgentID = @AgentID 
		WHERE CXCRMID = @CXCRMID AND AgentLeadID = @AgentLeadID
		
		SET @Counter = @Counter + 1
		--SELECT @AgentLeadID = SCOPE_IDENTITY()
		Select @Counter [Counter]
	
	FETCH NEXT FROM curCallBack INTO @CXCRMID, @AgentLeadID, @AgentID
	END
	CLOSE curCallBack
	DEALLOCATE curCallBack
	
	